﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JohnnySins.Entities;
using Dapper;

namespace JohnnySins.DAL.Repositories
{
    public class PhotoSetRepository : BaseRepository<PhotoSet>
    {
        public override IEnumerable<PhotoSet> FindAll()
        {
            IEnumerable<PhotoSet> result = Connection.Query<PhotoSet>(@"
                    SELECT PhotoSetId, PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic
                    FROM [PhotoSet] (nolock)");
            foreach (PhotoSet item in result)
                item.Photos = FindPhoto(item.PhotoSetId, null, null);
            return result;
        }

        public IEnumerable<PhotoSet> FindActive()
        {
            IEnumerable<PhotoSet> result = Connection.Query<PhotoSet>(@"
                    SELECT PhotoSetId, PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic
                    FROM [PhotoSet] (nolock) where IsActive = 1");
            foreach (PhotoSet item in result)
                item.Photos = FindPhoto(item.PhotoSetId, null, null);
            return result;
        }

        public IEnumerable<PhotoSet> FindActiveMember()
        {
            IEnumerable<PhotoSet> result = Connection.Query<PhotoSet>(@"
                    SELECT PhotoSetId, PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic 
                    from [PhotoSet] (nolock) 
                    where (IsActive = 1)
                        and (IsMember = 1)");
            foreach (PhotoSet item in result)
                item.Photos = FindPhoto(item.PhotoSetId, null, true);
            return result;
        }

        public IEnumerable<PhotoSet> FindActivePublic()
        {
            IEnumerable<PhotoSet> result = Connection.Query<PhotoSet>(@"
                    SELECT PhotoSetId, PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic
                    from [PhotoSet] (nolock) 
                    where (IsActive = 1)
                        and (IsPublic = 1)");
            foreach (PhotoSet item in result)
                item.Photos = FindPhoto(item.PhotoSetId, true, null);
            return result;
        }

        public override PhotoSet Find(int id)
        {
            PhotoSet result = Connection.Query<PhotoSet>(
                @"select PhotoSetId, PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic
                from [PhotoSet] (nolock)
                where PhotoSetId = @PhotoSetId", new { PhotoSetId = id }).SingleOrDefault();
            result.Photos = FindPhoto(result.PhotoSetId, null, null);
            return result;
        }

        public IEnumerable<PhotoSetPhoto> FindPhoto(int id, bool? isPublic, bool? isMember)
        {
            return Connection.Query<PhotoSetPhoto>(@"SELECT PhotoSetPhotoId, PhotoSetId, PhotoSetPhotoFileName, IsPublic, IsMember, IsPublicPoster, IsMemberPoster
                    FROM [PhotoSetPhoto] (nolock)
                    where (PhotoSetId = @PhotoSetId)
                        and ((IsPublic = @IsPublic) or (@IsPublic is null))
                        and ((IsMember = @IsMember) or (@IsMember is null))", new { PhotoSetId = id, IsMember = isMember, IsPublic = isPublic });
        }

        public override void Insert(PhotoSet item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [PhotoSet] (PhotoSetTitle, PhotoSetDate, PhotoSetStarring, PhotoSetDescription, IsActive, IsMember, IsPublic) 
                VALUES (@PhotoSetTitle, @PhotoSetDate, @PhotoSetStarring, @PhotoSetDescription, @IsActive, @IsMember, @IsPublic); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.PhotoSetId = result;
        }

        public void InsertPhoto(PhotoSetPhoto item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [PhotoSetPhoto] (PhotoSetId, PhotoSetPhotoFileName) 
                VALUES (@PhotoSetId, @PhotoSetPhotoFileName); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.PhotoSetPhotoId = result;
        }

        public override void Update(PhotoSet item)
        {
            Connection.Execute(
                @"update [PhotoSet] set PhotoSetTitle = @PhotoSetTitle, PhotoSetDate = @PhotoSetDate, 
                    PhotoSetStarring = @PhotoSetStarring, PhotoSetDescription = @PhotoSetDescription, 
                    IsActive = @IsActive, IsMember = @IsMember, IsPublic = @IsPublic
                where PhotoSetId = @PhotoSetId", item);
        }

        public void Activate(int id)
        {
            Connection.Execute(
                @"update [PhotoSet] set IsActive = 1
                where PhotoSetId = @PhotoSetId", new { PhotoSetId = id });
        }

        public void Deactivate(int id)
        {
            Connection.Execute(
                @"update [PhotoSet] set IsActive = 0
                where PhotoSetId = @PhotoSetId", new { PhotoSetId = id });
        }

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete from [PhotoSet]
                    where PhotoSetId = @PhotoSetId", new { PhotoSetId = id });
        }

        public void DeletePhoto(int id)
        {
            Connection.Execute(
                @"delete from [PhotoSetPhoto]
                    where PhotoSetPhotoId = @PhotoSetPhotoId", new { PhotoSetPhotoId = id });
        }

        public void SetPhotoFlag(int id, string nameFlag, bool valueFlag)
        {
            Connection.Execute(
                @"update [PhotoSetPhoto] set " + nameFlag + " = @ValueFlag where PhotoSetPhotoId = @PhotoSetPhotoId", 
                new { PhotoSetPhotoId = id, @ValueFlag = valueFlag });
        }
    }
}
